package cn.rulian.car.outbill.controller;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import cn.rulian.base.auth.ann.Access;
import cn.rulian.base.common.dto.ExcelBook;
import cn.rulian.base.common.dto.ExcelSheet;
import cn.rulian.base.common.vo.Result;
import cn.rulian.car.outbill.bo.OutBill;
import cn.rulian.car.outbill.service.OutBillService;
import cn.rulian.car.outbill.vo.OutBillVo;

/**
 * 车辆外出单--回录
 */

@Access(authCode="outBillAll")
@Controller
@RequestMapping("/car/outbill/outBillAll")
public class OutBillAllController {
	
	@Autowired
	private OutBillService service;
	
	//private final Logger log = LoggerFactory.getLogger(getClass());
	
	private final String path="car/outbill/outbillAll_"; //页面基本路径

	/**
	 * 页面基本路径
	 */
	private String getPath()
	{
		return path;
	}
	
	/**
	 * 列表页面
	 */
	@RequestMapping("/listPage")
	public String listPage() {
		return this.getPath()+"list";
	}

	/**
	 * 查询
	 */
	@RequestMapping("/listData")
	@ResponseBody
	public Result listData(OutBillVo vo) {
		return service.queryBack(vo);
	}

	/**
	 * 详情页面
	 */
	@RequestMapping("/detailPage")
	public String detailPage(Model model, Long id) {
		model.addAttribute("bo", service.findOne(id));
		return "car/outbill/outbillApply_detail";
	}

	/**
	 * 导出Excel
	 * @throws Exception 
	 */
	@RequestMapping("/exportExcel")
	public void exportExcel(HttpServletResponse response,OutBillVo vo,String type) throws Exception {
		
		
		File file = new File("aaa.xls");
		jxl.write.WritableWorkbook book = Workbook.createWorkbook(file); //工作薄

		List<OutBill> allDataList=service.exportExcelQuery(vo); //取得导出数据
		ExcelBook<OutBill> excelBook = this.buildExcelData(allDataList, type); //按维度进行数据加工
		String bookName = excelBook.getName(); //Excel文件名称
		List<ExcelSheet<OutBill>> sheetList = excelBook.getSheetList(); //各Sheet列表
		if(sheetList!=null && sheetList.size() > 0)
		{
			for(ExcelSheet<OutBill> excelSheet:sheetList)
			{
				String sheetName = excelSheet.getName(); //名称
				List<OutBill> outBillList = excelSheet.getDataList(); //数据列表
				
				jxl.write.WritableSheet sheet = book.createSheet(sheetName, 0); //工作表
				
				//设置行高
				sheet.getSettings().setDefaultRowHeight(350); //设置所有行默认行高
				sheet.setRowView(0, 700);//第1行 高度
				sheet.setRowView(1, 400);//第2行 高度
				
				//设置个别列宽
				sheet.setColumnView(1, 16);//宽度设置：登记时间
				sheet.setColumnView(8, 16);//宽度设置：出发时间
				sheet.setColumnView(9, 16);//宽度设置：归队时间
				sheet.setColumnView(6, 12);//宽度设置：用车部门
				sheet.setColumnView(7, 16);//宽度设置：目的地
				sheet.setColumnView(18, 12);//宽度设置：车辆状况
				sheet.setColumnView(19, 16);//宽度设置：备注
				
				//定义格式
				//主标题格式
				WritableCellFormat formatterTitle= new WritableCellFormat(); //标题格式
				formatterTitle.setAlignment(Alignment.CENTRE);//水平对齐
				formatterTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
				//WritableFont font = new WritableFont(WritableFont.createFont("ＭＳ Ｐゴシック"), 18, WritableFont.BOLD, false); //黑体
				WritableFont font = new WritableFont(WritableFont.TAHOMA, 18, WritableFont.BOLD, false);
				formatterTitle.setFont(font);
				
				//列标题格式
				WritableCellFormat formatterColumnTitle= new WritableCellFormat(); //列标题格式
				formatterColumnTitle.setAlignment(Alignment.CENTRE);//水平对齐
				formatterColumnTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
				formatterColumnTitle .setBackground(Colour.GRAY_25);//背景色
				formatterColumnTitle.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线  所有边框 细线

				//普通内容格式
				WritableCellFormat formatterCon= new WritableCellFormat(); //内容格式
				formatterCon.setAlignment(Alignment.CENTRE);//水平对齐
				formatterCon.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
				formatterCon.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线  所有边框 细线
				
				//合计行格式
				WritableCellFormat formatterSum= new WritableCellFormat(); //合计格式
				formatterSum.setAlignment(Alignment.CENTRE);//水平对齐
				formatterSum.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直对齐
				formatterSum.setBorder(Border.ALL, BorderLineStyle.THIN);//边框线  所有边框 细线
				WritableFont font_sum = new WritableFont(WritableFont.TAHOMA, 10, WritableFont.BOLD, false);
				formatterSum.setFont(font_sum);
				
				//输出标题
				sheet.mergeCells(0, 0, 20, 0); //合并单元格（标题）
				sheet.addCell(creatLabel(0,0,"车辆外出登记表（"+sheetName+"）",formatterTitle));

				//输出列标题
				int rowNum=1; //行号
				int columnNum=0; //列号
				sheet.addCell(creatLabel(rowNum,columnNum++,"序号",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"创建时间",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"车辆",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"驾驶员",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"用车人",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"审批人",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"用车部门",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"目的地",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"出发时间",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"归队时间",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"总时长",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"等待时长",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"出发里程",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"归队里程",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"使用里程",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"加油金额",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"过/停费",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"总费用",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"车辆状况",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"备注",formatterColumnTitle));
				sheet.addCell(creatLabel(rowNum,columnNum++,"来源",formatterColumnTitle));
				
				//合计项
				int useNum = 0; // 使用里程
				double useTime = 0; // 使用时长(小时，保留一位小数)
				double waitTime = 0; // 等待时间(小时，保留一位小数)
				double fuelNum = 0; // 加油费
				double otherFee = 0; // 其他费用（过路费、停车费）
				double allFee = 0; // 全部费用
			
				if(outBillList != null && outBillList.size() > 0)
				{
					SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm"); //日期格式化
					for(int i = 0; i < outBillList.size(); i++)
			    	{
						OutBill bo = outBillList.get(i); 
						rowNum++;
						columnNum=0;
						
						//非必填项Null值处理
						Double fuelNumDouble = bo.getFuelNum(); //加油费
						Double otherFeeDouble = bo.getOtherFee(); //其他费用（过路费、停车费）
						String remark = bo.getRemark(); //备注
						String  carStatus = bo.getCarStatus(); //车辆状况
						
						String fuelNumStr = ""; //
						if(fuelNumDouble != null){fuelNumStr = fuelNumDouble.toString();}
						String otherFeeStr = ""; //
						if(otherFeeDouble != null){otherFeeStr = otherFeeDouble.toString();}
						if(remark == null){remark="";}
						if(carStatus == null){remark="";}
						
						//输出一行数据
			    		sheet.addCell(creatLabel(rowNum,columnNum++,(i+1)+"",formatterCon)); //序号
			    		sheet.addCell(creatLabel(rowNum,columnNum++,formatter.format(bo.getAddTime()) ,formatterCon)); //登记时间
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getCar().getName() ,formatterCon)); //车辆
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getDriver() ,formatterCon)); //驾驶员
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getUser() ,formatterCon)); //用车人
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getApprover(),formatterCon)); //审批人
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getOrgan().getName() ,formatterCon)); //用车部门
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getDestination() ,formatterCon)); //目的地
			    		sheet.addCell(creatLabel(rowNum,columnNum++,formatter.format(bo.getBeginTime()) ,formatterCon)); //出发时间
			    		sheet.addCell(creatLabel(rowNum,columnNum++,formatter.format(bo.getEndTime()) ,formatterCon)); //归队时间
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getUseTime()+"" ,formatterCon)); //总时长
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getWaitTime()+"" ,formatterCon)); //等待时长
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getBeginNum()+"" ,formatterCon)); //出发里程
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getEndNum()+"" ,formatterCon)); //归队里程
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getUseNum()+"" ,formatterCon)); //使用里程
			    		sheet.addCell(creatLabel(rowNum,columnNum++,fuelNumStr ,formatterCon)); //加油金额
			    		sheet.addCell(creatLabel(rowNum,columnNum++,otherFeeStr ,formatterCon)); //过 / 停费
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getAllFee()+"" ,formatterCon)); //总费用
			    		sheet.addCell(creatLabel(rowNum,columnNum++,carStatus ,formatterCon)); //车辆状况
			    		sheet.addCell(creatLabel(rowNum,columnNum++,remark,formatterCon)); //备注
			    		sheet.addCell(creatLabel(rowNum,columnNum++,bo.getDataSource(),formatterCon)); //来源
			    		
			    		//合计项累加
			    		if(bo.getUseNum() != null)
						{
							useNum += bo.getUseNum();
						}
						if(bo.getUseTime() != null)
						{
							useTime += bo.getUseTime();
						}
						if(bo.getWaitTime() != null)
						{
							waitTime += bo.getWaitTime();
						}
						if(bo.getFuelNum() != null)
						{
							fuelNum += bo.getFuelNum();
						}
						if(bo.getOtherFee() != null)
						{
							otherFee += bo.getOtherFee();
						}
						if(bo.getAllFee() != null)
						{
							allFee += bo.getAllFee();
						}
			    	}
					
					//表格增加合计项
					rowNum++;
					columnNum = 0;
					sheet.setRowView(rowNum, 400);//合计行 高度
					
					sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //序号
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //登记时间
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //车辆
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //驾驶员
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //用车人
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //审批人
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //用车部门
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //目的地
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //出发时间
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"合计",formatterSum)); //归队时间
		    		sheet.addCell(creatLabel(rowNum,columnNum++,useTime+"" ,formatterSum)); //总时长
		    		sheet.addCell(creatLabel(rowNum,columnNum++,waitTime+"" ,formatterSum)); //等待时长
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //出发里程
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //归队里程
		    		sheet.addCell(creatLabel(rowNum,columnNum++,useNum+"" ,formatterSum)); //使用里程
		    		sheet.addCell(creatLabel(rowNum,columnNum++,fuelNum+"" ,formatterSum)); //加油金额
		    		sheet.addCell(creatLabel(rowNum,columnNum++,otherFee+"" ,formatterSum)); //过 / 停费
		    		sheet.addCell(creatLabel(rowNum,columnNum++,allFee+"" ,formatterSum)); //总费用
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"" ,formatterSum)); //车辆状况
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //备注
		    		sheet.addCell(creatLabel(rowNum,columnNum++,"",formatterSum)); //来源
		    		
				}
			}
		}
		
		//-------------------------------

		book.write();
		book.close();

		FileInputStream f = new FileInputStream(file);
		byte[] fb = new byte[f.available()];
		f.read(fb);
		response.setHeader("Content-disposition", "attachment; filename=" + new String((bookName + ".xls").getBytes("gb2312"), "iso8859-1"));
		ByteArrayInputStream bais = new ByteArrayInputStream(fb);
		int b;
		while ((b = bais.read()) != -1)
		{
			response.getOutputStream().write(b);
		}
		response.getOutputStream().flush();
		f.close();
		
	}
	
	private Label creatLabel(int rowNum,int columnNum,String con,WritableCellFormat formatter) //行，列，内容 ，格式化
	{  
		 return new Label(columnNum, rowNum, con,formatter);   
	}  
	
	/**
	 * 从不同维度，构建Excel导出所需要的数据
	 */
	private ExcelBook<OutBill> buildExcelData(List<OutBill> list,String type)
	{
		ExcelBook<OutBill> book = new ExcelBook<OutBill>();
		String typeOrgan="organ"; //部门维度
		String carOrgan="car"; //车辆维度
		String driverOrgan="driver"; //司机维度
		
		//确定Excel文件名
		if(typeOrgan.equals(type))
		{//部门维度
			book.setName("出车记录表（分部门）");
		}
		else if(carOrgan.equals(type))
		{//车辆维度
			book.setName("出车记录表（分车辆）");
		}
		else if(driverOrgan.equals(type))
		{//司机维度
			book.setName("出车记录表（分司机）");
		}
		else
		{//不分表
			book.setName("出车记录表");
		}

		//根据维度（sheetName）不同，对数据分表
		Map<String,List<OutBill>> map =new HashMap<String,List<OutBill>>(); 
		String sheetName = null;
		for(OutBill bo:list)
		{
			if(typeOrgan.equals(type))
			{//部门维度
				sheetName = bo.getOrgan().getName();
			}
			else if(carOrgan.equals(type))
			{//车辆维度
				sheetName = bo.getCar().getName();
			}
			else if(driverOrgan.equals(type))
			{//司机维度
				sheetName = bo.getDriver();
			}
			else
			{//不分表
				sheetName="全部";
			}
			
			List<OutBill> dataList = map.get(sheetName);
			if(dataList==null)
			{
				dataList = new ArrayList<>();
			}
			dataList.add(bo);
			map.put(sheetName, dataList);
		}
		
		//转换Map,得到数据表列表
		List<ExcelSheet<OutBill>> sheetList = new ArrayList<>();
		for (Map.Entry<String, List<OutBill>> m : map.entrySet()) 
		{
			ExcelSheet<OutBill> sheet = new ExcelSheet<OutBill>();
			sheet.setName(m.getKey());
			sheet.setDataList(m.getValue());
			sheetList.add(sheet);
		} 
		
		if(sheetList.size()==0)
		{
			ExcelSheet<OutBill> sheet = new ExcelSheet<OutBill>();
			sheet.setName("全部");
			sheet.setDataList(new ArrayList<OutBill>());
			sheetList.add(sheet);
		}
		
		book.setSheetList(sheetList);
		
		return book;
	}
}
